﻿<?xml version="1.0" encoding="utf-8"?>
<Vulcan xmlns="http://tempuri.org/vulcan2.xsd">
  <Connections>
    <Connection Name="TestConnection1" Type="OleDB" Server="localhost" Database="tempdb" Provider="SQLNCLI10" Authentication="Windows"/>
  </Connections>
  <Packages>
    <Package Name="Test_AstLower_IsNullPatcher" Type="ETL">
      <Tasks>
        <ETL Name="Test_AstLower_IsNullPatcher">
          <Transformations>
            <QuerySource Name="QuerySource" ConnectionName="TestConnection1">
              <Query QueryType="Standard">
                <Body>
                  SELECT CAST(1 AS INT) AS TestInt, CAST('12/01/1999' AS DATETIME) AS TestDate, N'Test' AS TestString
                </Body>
              </Query>
            </QuerySource>
            <!-- 
            For testing purposes, PatchNull doesn't know what the DerivedColumnType should be, 
            the ReplaceExisting flag basically tells SSIS to take the metadata type and override whatever I specify
            below
            -->
            <DerivedColumns Name="Patch Nulls1" ValidateExternalMetadata="false">
              <Columns>
                <Column Name="TestInt" Type="Object" ReplaceExisting="true">ISNULL(TestInt) ? -1 : TestInt</Column>
                <Column Name="TestDate" Type="Object" ReplaceExisting="true">ISNULL(TestDate) ? (DT_DBTIMESTAMP)"12/12/1999" : TestDate</Column>
                <Column Name="TestString" Type="Object" ReplaceExisting="true">ISNULL(TestString) ? "UNKNOWN" : TestString</Column>
              </Columns>
            </DerivedColumns>
            <DerivedColumns Name="Patch Nulls2" ValidateExternalMetadata="true">
              <Columns>
                <Column Name="TestInt" Type="Object" ReplaceExisting="true">ISNULL(TestInt) ? -1 : TestInt</Column>
                <Column Name="TestDate" Type="Object" ReplaceExisting="true">ISNULL(TestDate) ? (DT_DBTIMESTAMP)"12/12/1999" : TestDate</Column>
                <Column Name="TestString" Type="Object" ReplaceExisting="true">ISNULL(TestString) ? "UNKNOWN" : TestString</Column>
              </Columns>
            </DerivedColumns>
          </Transformations>
        </ETL>
      </Tasks>
    </Package>
  </Packages>
</Vulcan>